﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using bmapi.Models.DataEntity;
using Oracle.ManagedDataAccess.Client;

namespace bmapi.SQLServerAccess
{
    public class OracleHelp
    {
        public static readonly string connstr = ConfigurationManager.ConnectionStrings["ORCLconnectStr"].ToString();
        #region  查询一个值的封装
        public static string ExecuteScalar(OrcCommand orccmd)
        {
            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(orccmd.Cmd, conn);
                if (orccmd.Paras != null)
                    cmd.Parameters.AddRange(orccmd.Paras);
                Object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if (obj != null)
                {
                    return obj.ToString();
                }
                else
                {
                    return null;
                }

            }

        }

        #endregion

        #region 查询多个值的封装
        public static DataTable ExecuteScalars(OrcCommand orccmd)
        {
            DataSet set = new DataSet();
            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(orccmd.Cmd, conn);
                if (orccmd.Paras != null)
                {
                    cmd.Parameters.AddRange(orccmd.Paras);
                }
                using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                {
                    adapter.Fill(set);
                }
                cmd.Parameters.Clear();
                DataTable dt = set.Tables[0];
                if (dt.Rows.Count > 0)
                    return dt;
                else
                    return null;
            }
        }
        #endregion

        #region 非查询方法的封装 
        public static int ExecuteNonQuery(OrcCommand orccmd)
        {
            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(orccmd.Cmd, conn);
                if (orccmd.Paras != null)
                {
                    cmd.Parameters.AddRange(orccmd.Paras);
                }
                int result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return result;
            }
        }
        #endregion

        /// <summary>  
        /// 执行事务，返回0或者1  
        /// </summary>  
        /// <param name="Sqlstr">sql语句</param>  
        /// <returns></returns>  
        public static int ExecTran(List<OrcCommand> orccmds)
        {
            int res = 0;
            OracleConnection conn = new OracleConnection(connstr);
            conn.Open();
            // 先实例SqlTransaction类，使用这个事务使用的是con 这个连接，使用BeginTransaction这个方法来开始执行这个事务
            OracleTransaction tran = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.Transaction = tran;
            try
            {
                for (int i = 0; i < orccmds.Count; i++)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = orccmds[i].Cmd;
                    cmd.Parameters.AddRange(orccmds[i].Paras);
                    res = cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return 1;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return 0;
            }
            finally
            {
                conn.Close();
                tran.Dispose();
            }
        }

    }
}